package com.sailfish.springbootdemo.dao.db1;

import com.sailfish.springbootdemo.common.DB1TableName;
import com.sailfish.springbootdemo.pojo.db1.PcbaDeviceResource;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import java.util.List;

public interface PcbaDeviceResourceDao {

    /**
     * 根据设别类型 + 搜索条件 查询
     *
     * @param searchValue
     * @param pcbaDeviceTypeId
     * @return
     */
    @Results(id = "pcbaDeviceResourceResultMap", value = {
            @Result(column = "pcba_device_resource_id", property = "pcbaDeviceResourceId", jdbcType = JdbcType.INTEGER),
            @Result(column = "pcba_device_type_id", property = "pcbaDeviceTypeId", jdbcType = JdbcType.INTEGER),
            @Result(column = "brand_id", property = "brandId", jdbcType = JdbcType.INTEGER),
            @Result(column = "brand_type_id", property = "brandTypeId", jdbcType = JdbcType.INTEGER),
            @Result(column = "sort", property = "sort", jdbcType = JdbcType.INTEGER),
            @Result(column = "create_time", property = "createTime", jdbcType = JdbcType.DATETIMEOFFSET),
            @Result(column = "update_time", property = "updateTime", jdbcType = JdbcType.DATETIMEOFFSET),
            @Result(column = "status", property = "status", jdbcType = JdbcType.INTEGER),
            @Result(column = "notes", property = "notes", jdbcType = JdbcType.VARCHAR),
            @Result(column = "pcba_device_picture", property = "pcbaDevicePicture", jdbcType = JdbcType.VARCHAR),
            @Result(column = "archives_link", property = "archivesLink", jdbcType = JdbcType.VARCHAR),
            @Result(column = "is_filled_base_params", property = "isFilledBaseParams", jdbcType = JdbcType.INTEGER),
            @Result(column = "is_filled_tech_params", property = "isFilledTechParams", jdbcType = JdbcType.INTEGER),
            @Result(column = "brand_name", property = "brandName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "brand_type_name", property = "brandTypeName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "pic_length", property = "picLength", jdbcType = JdbcType.INTEGER),
            @Result(column = "pic_width", property = "picWidth", jdbcType = JdbcType.INTEGER),

            @Result(column = "audit_status_base", property = "auditStatusBase", jdbcType = JdbcType.INTEGER),
            @Result(column = "audit_status_tech", property = "auditStatusTech", jdbcType = JdbcType.INTEGER),
            @Result(column = "submitter_base", property = "submitterBase", jdbcType = JdbcType.INTEGER),
            @Result(column = "submitter_tech", property = "submitterTech", jdbcType = JdbcType.INTEGER),
            @Result(column = "auditor_base", property = "auditorBase", jdbcType = JdbcType.INTEGER),
            @Result(column = "auditor_tech", property = "auditorTech", jdbcType = JdbcType.INTEGER),
            @Result(column = "audit_remark_base", property = "auditRemarkBase", jdbcType = JdbcType.VARCHAR),
            @Result(column = "audit_remark_tech", property = "auditRemarkTech", jdbcType = JdbcType.VARCHAR),
    })
    @Select("<script>" +
            "select t1.* from " + DB1TableName.PCBA_DEVICE_RESOURCE + " t1 left join " + DB1TableName.PCBA_DEVICE_TYPE + " t4 on t1.pcba_device_type_id = t4.pcba_device_type_id left join " + DB1TableName.BRAND + " t2 on t1.brand_id = t2.brand_id  left join " + DB1TableName.BRAND_TYPE + " t3 on t1.brand_type_id = t3.brand_type_id where 1 = 1 " +
            "<if test='searchValue != null and searchValue != \"\" '>" +
            " and (t2.brand_name like CONCAT('%',#{searchValue},'%') or t3.brand_type_name like CONCAT('%',#{searchValue},'%') or t1.notes like CONCAT('%',#{searchValue},'%'))" +
            "</if>" +
            "<if test='pcbaDeviceTypeId != null and pcbaDeviceTypeId != \"\" '>" +
            " and t1.pcba_device_type_id = #{pcbaDeviceTypeId}" +
            "</if>" +
            "<if test='pcbaDeviceResourceId != null and pcbaDeviceResourceId != \"\" '>" +
            " and t1.pcba_device_resource_id = #{pcbaDeviceResourceId}" +
            "</if>" +
            "<if test='status != null and status != \"\" '>" +
            " and t1.status = #{status}" +
            "</if>" +
            "<if test='isTechOwn != null and isTechOwn != \"\" '>" +
            " and t4.is_tech_own = #{isTechOwn}" +
            "</if>" +
            "<if test='brandId != null and brandId != \"\" '>" +
            " and t1.brand_id = #{brandId}" +
            "</if>" +
            "<if test='brandTypeId != null and brandTypeId != \"\" '>" +
            " and t1.brand_type_id = #{brandTypeId}" +
            "</if>" +
            "<if test='notes != null '>" +
            " and t1.notes = #{notes}" +
            "</if>" +

            "<if test='auditStatusBase != null and auditStatusBase != \"\" '>" +
            " and t1.audit_status_base = #{auditStatusBase}" +
            "</if>" +
            "<if test='auditStatusTech != null and auditStatusTech != \"\" '>" +
            " and t1.audit_status_tech = #{auditStatusTech}" +
            "</if>" +

            " order by t4.is_tech_own desc,t1.create_time desc" +
            "<if test='begin != null and pageSize != null and pageSize != \"\" '>" +
            " limit #{begin}, #{pageSize}" +
            "</if>" +
            "</script>")
    List<PcbaDeviceResource> getAllByDeviceTypeAndSearch(@Param("searchValue") String searchValue, @Param("pcbaDeviceTypeId") Integer pcbaDeviceTypeId, @Param("pcbaDeviceResourceId") Integer pcbaDeviceResourceId, @Param("isTechOwn") Integer isTechOwn, @Param("status") Integer status, @Param("brandId") Integer brandId, @Param("brandTypeId") Integer brandTypeId, @Param("begin") Integer begin, @Param("pageSize") Integer pageSize, @Param("notes") String notes, @Param("auditStatusBase") Integer auditStatusBase, @Param("auditStatusTech") Integer auditStatusTech);

    /**
     * 查询所有设备资源
     *
     * @return
     */
    @Select("select * from " + DB1TableName.PCBA_DEVICE_RESOURCE + " where brand_id is not null and brand_type_id is not null and brand_id != '' and brand_type_id != '' ")
    @ResultMap(value = "pcbaDeviceResourceResultMap")
    List<PcbaDeviceResource> getAllPcbaDeviceResource();

    /**
     * 根据品牌或者型号查询
     *
     * @param brandId
     * @param brandTypeId
     * @return
     */
    @Select("<script>" +
            " select * from " + DB1TableName.PCBA_DEVICE_RESOURCE + " where 1 = 1 " +
            "<if test='brandId != null and brandId != \"\" '>" +
            " and brand_id = #{brandId}" +
            "</if>" +
            "<if test='brandTypeId != null and brandTypeId != \"\" '>" +
            " and brand_type_id = #{brandTypeId}" +
            "</if>" +
            "</script>")
    List<PcbaDeviceResource> getAllByBrandOrBrandType(@Param("brandId") Integer brandId, @Param("brandTypeId") Integer brandTypeId);

    /**
     * 根据id查询
     *
     * @param pcbaDeviceResourceId
     * @return
     */
    @ResultMap(value = "pcbaDeviceResourceResultMap")
    @Select("select * from " + DB1TableName.PCBA_DEVICE_RESOURCE + " where pcba_device_resource_id = #{pcbaDeviceResourceId}")
    PcbaDeviceResource getById(@Param("pcbaDeviceResourceId") Integer pcbaDeviceResourceId);

    /**
     * 删除
     *
     * @param pcbaDeviceResourceId
     * @return
     */
    @Delete("delete from " + DB1TableName.PCBA_DEVICE_RESOURCE + " where pcba_device_resource_id = #{pcbaDeviceResourceId}")
    Integer deleteById(@Param("pcbaDeviceResourceId") Integer pcbaDeviceResourceId);

    /**
     * 新增
     *
     * @param pcbaDeviceResource
     * @return
     */
    @Insert("insert into " + DB1TableName.PCBA_DEVICE_RESOURCE + "(brand_id,brand_type_id,status,pcba_device_type_id,sort,create_time,notes,pcba_device_picture,archives_link,is_filled_base_params,is_filled_tech_params,pic_length,pic_width,audit_status_base,audit_status_tech) values " +
            "(#{pcbaDeviceResource.brandId},#{pcbaDeviceResource.brandTypeId},#{pcbaDeviceResource.status},#{pcbaDeviceResource.pcbaDeviceTypeId},#{pcbaDeviceResource.sort},#{pcbaDeviceResource.createTime},#{pcbaDeviceResource.notes},#{pcbaDeviceResource.pcbaDevicePicture},#{pcbaDeviceResource.archivesLink},#{pcbaDeviceResource.isFilledBaseParams},#{pcbaDeviceResource.isFilledTechParams},#{pcbaDeviceResource.picLength},#{pcbaDeviceResource.picWidth},#{pcbaDeviceResource.auditStatusBase},#{pcbaDeviceResource.auditStatusTech})")
    // 显式设置主键
    @Options(useGeneratedKeys = true, keyProperty = "pcbaDeviceResource.pcbaDeviceResourceId")
    Integer addPcbaDeviceResource(@Param("pcbaDeviceResource") PcbaDeviceResource pcbaDeviceResource);

    /**
     * 更新
     *
     * @param pcbaDeviceResource
     * @return
     */
    @Update("<script>" +
            "update " + DB1TableName.PCBA_DEVICE_RESOURCE +
            "<set>" +
            "<if test='pcbaDeviceResource.brandId != null and pcbaDeviceResource.brandId != \"\" '>" +
            " brand_id = #{pcbaDeviceResource.brandId}," +
            "</if>" +
            "<if test='pcbaDeviceResource.brandTypeId != null and pcbaDeviceResource.brandTypeId != \"\" '>" +
            " brand_type_id = #{pcbaDeviceResource.brandTypeId}," +
            "</if>" +
            "<if test='pcbaDeviceResource.updateTime != null '>" +
            " update_time = #{pcbaDeviceResource.updateTime}," +
            "</if>" +
            "<if test='pcbaDeviceResource.status != null and pcbaDeviceResource.status != \"\" '>" +
            " status = #{pcbaDeviceResource.status}," +
            "</if>" +
            "<if test='pcbaDeviceResource.pcbaDeviceTypeId != null and pcbaDeviceResource.pcbaDeviceTypeId != \"\" '>" +
            " pcba_device_type_id = #{pcbaDeviceResource.pcbaDeviceTypeId}," +
            "</if>" +
            "<if test='pcbaDeviceResource.sort != null and pcbaDeviceResource.sort != \"\" '>" +
            " sort = #{pcbaDeviceResource.sort}," +
            "</if>" +
            "<if test='pcbaDeviceResource.notes != null and pcbaDeviceResource.notes != \"\" '>" +
            " notes = #{pcbaDeviceResource.notes}," +
            "</if>" +
            "<if test='pcbaDeviceResource.pcbaDevicePicture != null and pcbaDeviceResource.pcbaDevicePicture != \"\" '>" +
            " pcba_device_picture = #{pcbaDeviceResource.pcbaDevicePicture}," +
            "</if>" +
            "<if test='pcbaDeviceResource.archivesLink != null and pcbaDeviceResource.archivesLink != \"\" '>" +
            " archives_link = #{pcbaDeviceResource.archivesLink}," +
            "</if>" +
            "<if test='pcbaDeviceResource.isFilledBaseParams != null and pcbaDeviceResource.isFilledBaseParams != \"\" '>" +
            " is_filled_base_params = #{pcbaDeviceResource.isFilledBaseParams}," +
            "</if>" +
            "<if test='pcbaDeviceResource.isFilledTechParams != null and pcbaDeviceResource.isFilledTechParams != \"\" '>" +
            " is_filled_tech_params = #{pcbaDeviceResource.isFilledTechParams}," +
            "</if>" +
            "<if test='pcbaDeviceResource.picLength != null and pcbaDeviceResource.picLength != \"\" '>" +
            " pic_length = #{pcbaDeviceResource.picLength}," +
            "</if>" +
            "<if test='pcbaDeviceResource.picWidth != null and pcbaDeviceResource.picWidth != \"\" '>" +
            " pic_width = #{pcbaDeviceResource.picWidth}," +
            "</if>" +
            "<if test='pcbaDeviceResource.auditStatusBase != null and pcbaDeviceResource.auditStatusBase != \"\" '>" +
            " audit_status_base = #{pcbaDeviceResource.auditStatusBase}," +
            "</if>" +
            "<if test='pcbaDeviceResource.auditStatusTech != null and pcbaDeviceResource.auditStatusTech != \"\" '>" +
            " audit_status_tech = #{pcbaDeviceResource.auditStatusTech}," +
            "</if>" +
            "<if test='pcbaDeviceResource.submitterBase != null and pcbaDeviceResource.submitterBase != \"\" '>" +
            " submitter_base = #{pcbaDeviceResource.submitterBase}," +
            "</if>" +
            "<if test='pcbaDeviceResource.submitterTech != null and pcbaDeviceResource.submitterTech != \"\" '>" +
            " submitter_tech = #{pcbaDeviceResource.submitterTech}," +
            "</if>" +
            "<if test='pcbaDeviceResource.auditorBase != null and pcbaDeviceResource.auditorBase != \"\" '>" +
            " auditor_base = #{pcbaDeviceResource.auditorBase}," +
            "</if>" +
            "<if test='pcbaDeviceResource.auditorTech != null and pcbaDeviceResource.auditorTech != \"\" '>" +
            " auditor_tech = #{pcbaDeviceResource.auditorTech}," +
            "</if>" +
            "<if test='pcbaDeviceResource.auditRemarkBase != null and pcbaDeviceResource.auditRemarkBase != \"\" '>" +
            " audit_remark_base = #{pcbaDeviceResource.auditRemarkBase}," +
            "</if>" +
            "<if test='pcbaDeviceResource.auditRemarkTech != null and pcbaDeviceResource.auditRemarkTech != \"\" '>" +
            " audit_remark_tech = #{pcbaDeviceResource.auditRemarkTech}," +
            "</if>" +
            "</set>" +
            " where pcba_device_resource_id = #{pcbaDeviceResource.pcbaDeviceResourceId}" +
            "</script>")
    Integer updatePcbaDeviceResource(@Param("pcbaDeviceResource") PcbaDeviceResource pcbaDeviceResource);

    @Update("update " + DB1TableName.PCBA_DEVICE_RESOURCE + " set audit_status_base = #{auditStatusBase},submitter_base = #{submitterBase} where pcba_device_resource_id = #{pcbaDeviceResourceId}")
    Integer updateAuditStatusBaseWithSubmitter(@Param("pcbaDeviceResourceId") Integer pcbaDeviceResourceId, @Param("auditStatusBase") Integer auditStatusBase, @Param("submitterBase") Integer submitterBase);

    @Update("update " + DB1TableName.PCBA_DEVICE_RESOURCE + " set audit_status_tech = #{auditStatusTech},submitter_tech = #{submitterTech} where pcba_device_resource_id = #{pcbaDeviceResourceId}")
    Integer updateAuditStatusTechWithSubmitter(@Param("pcbaDeviceResourceId") Integer pcbaDeviceResourceId, @Param("auditStatusTech") Integer auditStatusTech, @Param("submitterTech") Integer submitterTech);

    @Update("update " + DB1TableName.PCBA_DEVICE_RESOURCE + " set audit_status_base = #{auditStatusBase},audit_remark_base = #{auditRemarkBase},auditor_base = #{auditorBase} where pcba_device_resource_id = #{pcbaDeviceResourceId}")
    Integer updateAuditStatusBaseWithAuditor(@Param("pcbaDeviceResourceId") Integer pcbaDeviceResourceId, @Param("auditStatusBase") Integer auditStatusBase, @Param("auditRemarkBase") String auditRemarkBase, @Param("auditorBase") Integer auditorBase);

    @Update("update " + DB1TableName.PCBA_DEVICE_RESOURCE + " set audit_status_tech = #{auditStatusTech},audit_remark_tech = #{auditRemarkTech},auditor_tech = #{auditorTech} where pcba_device_resource_id = #{pcbaDeviceResourceId}")
    Integer updateAuditStatusTechWithAuditor(@Param("pcbaDeviceResourceId") Integer pcbaDeviceResourceId, @Param("auditStatusTech") Integer auditStatusTech, @Param("auditRemarkTech") String auditRemarkTech, @Param("auditorTech") Integer auditorTech);

    /**
     * 设备类型 + 品牌 + 型号 + 备注 确定唯一性
     *
     * @param pcbaDeviceTypeId
     * @param brandId
     * @param brandTypeId
     * @param notes
     * @return
     */
    @ResultMap(value = "pcbaDeviceResourceResultMap")
    @Select("<script>" +
            "select * from " + DB1TableName.PCBA_DEVICE_RESOURCE + " where 1 = 1 " +
            "<if test='pcbaDeviceTypeId != null and pcbaDeviceTypeId != \"\" '>" +
            " and pcba_device_type_id = #{pcbaDeviceTypeId}" +
            "</if>" +
            "<if test='brandId != null and brandId != \"\" '>" +
            " and brand_id = #{brandId}" +
            "</if>" +
            "<if test='brandTypeId != null and brandTypeId != \"\" '>" +
            " and brand_type_id = #{brandTypeId}" +
            "</if>" +
            "<if test='notes != null and notes != \"\" '>" +
            " and notes = #{notes}" +
            "</if>" +
            "<if test='pcbaDeviceResourceId != null and pcbaDeviceResourceId != \"\" '>" +
            " and pcba_device_resource_id != #{pcbaDeviceResourceId}" +
            "</if>" +
            " limit 1" +
            "</script>")
    PcbaDeviceResource getByConditionsJudgeUnique(@Param("pcbaDeviceTypeId") Integer pcbaDeviceTypeId, @Param("brandId") Integer brandId, @Param("brandTypeId") Integer brandTypeId, @Param("notes") String notes, @Param("pcbaDeviceResourceId") Integer pcbaDeviceResourceId);

    @ResultMap(value = "pcbaDeviceResourceResultMap")
    @Select("<script>" +
            "select * from " + DB1TableName.PCBA_DEVICE_RESOURCE + " where 1 = 1 " +
            "<if test='pcbaDeviceTypeId != null and pcbaDeviceTypeId != \"\" '>" +
            " and pcba_device_type_id = #{pcbaDeviceTypeId}" +
            "</if>" +
            "<if test='brandId != null and brandId != \"\" '>" +
            " and brand_id = #{brandId}" +
            "</if>" +
            "<if test='brandTypeId != null and brandTypeId != \"\" '>" +
            " and brand_type_id = #{brandTypeId}" +
            "</if>" +
            " and (notes is NULL or notes = '') " +
            "<if test='pcbaDeviceResourceId != null and pcbaDeviceResourceId != \"\" '>" +
            " and pcba_device_resource_id != #{pcbaDeviceResourceId}" +
            "</if>" +
            " limit 1" +
            "</script>")
    PcbaDeviceResource getByConditionsWithoutNotes(@Param("pcbaDeviceTypeId") Integer pcbaDeviceTypeId, @Param("brandId") Integer brandId, @Param("brandTypeId") Integer brandTypeId, @Param("pcbaDeviceResourceId") Integer pcbaDeviceResourceId);

    /**
     * @param pcbaDeviceTypeId
     * @param brandId
     * @param brandTypeId
     * @param notes
     * @return
     */
    @ResultMap(value = "pcbaDeviceResourceResultMap")
    @Select("<script>" +
            "select * from " + DB1TableName.PCBA_DEVICE_RESOURCE + " where 1 = 1 " +
            "<if test='pcbaDeviceTypeId != null and pcbaDeviceTypeId != \"\" '>" +
            " and pcba_device_type_id = #{pcbaDeviceTypeId}" +
            "</if>" +
            "<if test='brandId != null and brandId != \"\" '>" +
            " and brand_id = #{brandId}" +
            "</if>" +
            "<if test='brandTypeId != null and brandTypeId != \"\" '>" +
            " and brand_type_id = #{brandTypeId}" +
            "</if>" +
            "<if test='notes != null and notes != \"\" '>" +
            " and notes = #{notes}" +
            "</if>" +
            "<if test='pcbaDeviceResourceId != null and pcbaDeviceResourceId != \"\" '>" +
            " and pcba_device_resource_id != #{pcbaDeviceResourceId}" +
            "</if>" +
            "</script>")
    List<PcbaDeviceResource> getByConditions(@Param("pcbaDeviceTypeId") Integer pcbaDeviceTypeId, @Param("brandId") Integer brandId, @Param("brandTypeId") Integer brandTypeId, @Param("notes") String notes, @Param("pcbaDeviceResourceId") Integer pcbaDeviceResourceId);

    /**
     * 特殊工艺里面根据 设备类型查询 resource_id
     *
     * @param pcbaDeviceTypeId
     * @return
     */
    @Select("select * from " + DB1TableName.PCBA_DEVICE_RESOURCE + " where pcba_device_type_id = #{pcbaDeviceTypeId}")
    @ResultMap(value = "pcbaDeviceResourceResultMap")
    List<PcbaDeviceResource> getPcbaDeviceResourceByDeviceTypeIdSpecial(@Param("pcbaDeviceTypeId") Integer pcbaDeviceTypeId);

    /**
     * 根据 设备类型查询 不重复的 品牌
     *
     * @param deviceType
     * @return
     */
    @ResultMap(value = "pcbaDeviceResourceResultMap")
    @Select("<script>" +
            "select distinct t1.brand_id,t1.brand_type_id from " + DB1TableName.PCBA_DEVICE_RESOURCE + " t1," + DB1TableName.PCBA_DEVICE_TYPE + " t2 where t1.pcba_device_type_id = t2.pcba_device_type_id" +
            "<if test='deviceType != null and deviceType != \"\" '>" +
            " and t1.pcba_device_type_id = #{deviceType}" +
            "</if>" +
            "<if test='isTechOwn != null and isTechOwn != \"\" '>" +
            " and t2.is_tech_own = #{isTechOwn}" +
            "</if>" +
            "<if test='status != null and status != \"\" '>" +
            " and t1.status = #{status}" +
            "</if>" +
            "</script>")
    List<PcbaDeviceResource> getAllBrandByDeviceType(@Param("deviceType") Integer deviceType, @Param("isTechOwn") Integer isTechOwn, @Param("status") Integer status);
}
